﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.Objects;
using System.Data.SqlClient;
using System.Linq;
using System.Web;

namespace JamesThew.Dashboard
{
    public class UserDB
    {
        public User Login(String UserName, String UserPassword)
        {
            User user;
            SqlConnection con = new SqlConnection();
            con.ConnectionString = JamesThew.Includes.Core.GetConnectionString();
            con.Open();
            SqlCommand cmd = new SqlCommand("SignIn", con);
            cmd.CommandType = CommandType.StoredProcedure;

            cmd.Parameters.Add(new SqlParameter("@UserLogin", UserName));
            cmd.Parameters.Add(new SqlParameter("@UserPass", UserPassword));

            SqlDataReader rd;

            rd = cmd.ExecuteReader();
            DataTable dt = new DataTable();

            dt.Load(rd);

            try
            {
                int UserID = (int)dt.Rows[0][0];
                String UserLogin = dt.Rows[0][1].ToString();
                String UserPass = dt.Rows[0][2].ToString();
                String UserDisplayName = dt.Rows[0][3].ToString();
                String UserNickName = dt.Rows[0][4].ToString();
                String UserEmail = dt.Rows[0][5].ToString();
                String UserURL = dt.Rows[0][6].ToString();
                String UserRegistered = dt.Rows[0][7].ToString();
                String UserActivationKey = dt.Rows[0][8].ToString();
                String UserStatus = dt.Rows[0][9].ToString();
                String UserAddress = dt.Rows[0][10].ToString();
                int UserLevel = (int)dt.Rows[0][11];

                user = new User(UserID, UserLogin, UserPass, UserDisplayName, UserNickName,
                    UserEmail, UserURL, UserRegistered, UserActivationKey, UserStatus, UserAddress, UserLevel);
            }
            catch (Exception ex) {
                user = new User();
            }

            con.Close();

            return user;
        }
        public List<User> Get() {
            List<User> lu = new List<User>();

            SqlConnection con = new SqlConnection();
            con.ConnectionString = JamesThew.Includes.Core.GetConnectionString();
            con.Open();
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = con;
            cmd.CommandText = "SELECT * FROM CMS_Users WHERE 1=1";
            SqlDataReader rd;

            rd = cmd.ExecuteReader();
            DataTable dt = new DataTable();

            dt.Load(rd);

            for (int i = 0; i < dt.Rows.Count; i++)
            {

                int UserID = (int)dt.Rows[i][0];
                String UserLogin = dt.Rows[i][1].ToString();
                String UserPass = dt.Rows[i][2].ToString();
                String UserDisplayName = dt.Rows[i][3].ToString();
                String UserNickName = dt.Rows[i][4].ToString();
                String UserEmail = dt.Rows[i][5].ToString();
                String UserURL = dt.Rows[i][6].ToString();
                String UserRegistered = dt.Rows[i][7].ToString();
                String UserActivationKey = dt.Rows[i][8].ToString();
                String UserStatus = dt.Rows[i][9].ToString();
                String UserAddress = dt.Rows[i][10].ToString();
                int UserLevel = (int) dt.Rows[i][11];

                User user = new User(UserID, UserLogin, UserPass, UserDisplayName, UserNickName,
                    UserEmail, UserURL, UserRegistered, UserActivationKey, UserStatus, UserAddress, UserLevel);
                
                lu.Add(user);
            }

            con.Close();

            return lu;
        }

        public Boolean Delete(int UserID)
        {

            SqlConnection con = new SqlConnection();
            con.ConnectionString = JamesThew.Includes.Core.GetConnectionString();
            con.Open();
            SqlCommand cmd = new SqlCommand("DeleteUser", con);
            cmd.CommandType = CommandType.StoredProcedure;

            cmd.Parameters.Add(new SqlParameter("@UserID", UserID));

            Int32 check = cmd.ExecuteNonQuery();
            con.Close();
            if (check > 0) return true;
            return false;
        }

        public Boolean Add(User user)
        {

            SqlConnection con = new SqlConnection();
            con.ConnectionString = JamesThew.Includes.Core.GetConnectionString();
            con.Open();
            SqlCommand cmd = new SqlCommand("InsertUser", con);
            cmd.CommandType = CommandType.StoredProcedure;

            cmd.Parameters.Add(new SqlParameter("@UserLogin", user.UserLogin));
            cmd.Parameters.Add(new SqlParameter("@UserPass", user.UserPass));
            cmd.Parameters.Add(new SqlParameter("@UserDisplayName", user.UserLevel));
            cmd.Parameters.Add(new SqlParameter("@UserNickName", user.UserLevel));
            cmd.Parameters.Add(new SqlParameter("@UserEmail", user.UserLevel));
            cmd.Parameters.Add(new SqlParameter("@UserURL", user.UserLevel));
            cmd.Parameters.Add(new SqlParameter("@UserRegistered", user.UserLevel));
            cmd.Parameters.Add(new SqlParameter("@UserActivationKey", user.UserLevel));
            cmd.Parameters.Add(new SqlParameter("@UserStatus", user.UserLevel));
            cmd.Parameters.Add(new SqlParameter("@UserAddress", user.UserLevel));
            cmd.Parameters.Add(new SqlParameter("@UserLevel", user.UserLevel));


            cmd.Parameters.Add(new SqlParameter("@UserID", SqlDbType.Int, 6));
            cmd.Parameters["@UserID"].Direction = ParameterDirection.Output;

            Int32 check = cmd.ExecuteNonQuery();
            string khoa = cmd.Parameters["@UserID"].Value.ToString();
            Int32 UserID = JamesThew.Includes.Core.ParseInt(khoa);

            user.UserID = UserID;

            con.Close();
            if (check > 0) return true;
            return false;

        }


        public bool Register(String UserLogin, String UserPass, String UserEmail, String PaymentMethod, String PaymentPackage)
        {

            SqlConnection con = new SqlConnection();
            con.ConnectionString = JamesThew.Includes.Core.GetConnectionString();
            con.Open();
            SqlCommand cmd = new SqlCommand("Register", con);
            cmd.CommandType = CommandType.StoredProcedure;

            cmd.Parameters.Add(new SqlParameter("@UserLogin", UserLogin));
            cmd.Parameters.Add(new SqlParameter("@UserPass", UserPass));
            cmd.Parameters.Add(new SqlParameter("@UserEmail", UserEmail));
            cmd.Parameters.Add(new SqlParameter("@PaymentMethod", PaymentMethod));
            cmd.Parameters.Add(new SqlParameter("@PaymentPackage", PaymentPackage));

            try
            {
                Int32 check = cmd.ExecuteNonQuery();
                con.Close();
                if (check > 0) return true;
                return false;
            }
            catch(Exception ex){
                return false;
            }
        }


        public Boolean Update(User user)
        {

            SqlConnection con = new SqlConnection();
            con.ConnectionString = JamesThew.Includes.Core.GetConnectionString();
            con.Open();
            SqlCommand cmd = new SqlCommand("UpdateUser", con);
            cmd.CommandType = CommandType.StoredProcedure;

            cmd.Parameters.Add(new SqlParameter("@UserID", user.UserID));
            cmd.Parameters.Add(new SqlParameter("@UserLogin", user.UserLogin));
            cmd.Parameters.Add(new SqlParameter("@UserPass", user.UserPass));
            cmd.Parameters.Add(new SqlParameter("@UserDisplayName", user.UserLevel));
            cmd.Parameters.Add(new SqlParameter("@UserNickName", user.UserLevel));
            cmd.Parameters.Add(new SqlParameter("@UserEmail", user.UserLevel));
            cmd.Parameters.Add(new SqlParameter("@UserURL", user.UserLevel));
            cmd.Parameters.Add(new SqlParameter("@UserRegistered", user.UserLevel));
            cmd.Parameters.Add(new SqlParameter("@UserActivationKey", user.UserLevel));
            cmd.Parameters.Add(new SqlParameter("@UserStatus", user.UserLevel));
            cmd.Parameters.Add(new SqlParameter("@UserAddress", user.UserLevel));
            cmd.Parameters.Add(new SqlParameter("@UserLevel", user.UserLevel));


            Int32 check = cmd.ExecuteNonQuery();
            con.Close();
            if (check > 0) return true;
            return false;

        }

        public String GetUserName(int UserID)
        {

            SqlConnection con = new SqlConnection();
            con.ConnectionString = JamesThew.Includes.Core.GetConnectionString();
            con.Open();
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = con;
            cmd.CommandText = "SELECT UserLogin FROM CMS_Users WHERE 1=1 AND UserID = " + UserID;
            SqlDataReader rd;

            rd = cmd.ExecuteReader();
            DataTable dt = new DataTable();

            dt.Load(rd);

            String UserName = "";

            try
            {
                UserName = dt.Rows[0][0].ToString();
            }
            catch (IndexOutOfRangeException)
            {

            }

            con.Close();

            return UserName;
        }


    }
}